<?php

/**
 * mysql 处理类
 * ============================================================================
 * 未定义
 * ============================================================================
 * $Author: pjl $
 * $Date: 2013-01-25 17:18:49 +0800 (周五, 2013-01-25) $
 * $Revision: 5 $
 * $Id: mysql.class.php 5 2013-01-25 09:18:49Z pjl $
 */

define("mysql.class", true);

class mysql{

	static					$config;
	protected static 		$pr;
	protected				$link;
	protected				$tb;
	protected 				$debug=true;
	protected 				$conditions;
	protected 				$conditionGround=0;
	protected 				$version;
	var						$result;
	var						$sql;

	/**
	 * @desc 默认处理,自动连接
	 */
	public function mysql(){
		$this->conn();
	}

	/**
	 * @desc 数据库连接
	 * @throws Exception
	 */
	public function conn(){
		try{
			if(!is_array(self::$config)){
				throw new Exception("db config must be a array");
			}
			else
			{
				$conf=self::$config;
				isset($conf['pr'])?self::$pr=$conf['pr']:'';
			}
			if ($this->pconnect)
			{
				if (!($this->link = @mysql_pconnect($conf['host'], $conf['name'], $conf['psw'])))
				{
					throw new Exception("Can't pConnect MySQL Server({$conf['host']})!");
					return false;
				}
			}
			else
			{
				if (PHP_VERSION >= '4.2')
				{
					$this->link = @mysql_connect($conf['host'], $conf['name'], $conf['psw'], true);
				}
				else
				{
					$this->link = @mysql_connect($conf['host'], $conf['name'], $conf['psw']);

					mt_srand((double)microtime() * 1000000); // 对 PHP 4.2 以下的版本进行随机数函数的初始化工作
				}
				if (!$this->link)
				{
					throw new Exception("Can't Connect MySQL Server({$conf['host']})!");
					return false;
				}
			}

			$this->version = mysql_get_server_info($this->link);

			/* 如果mysql 版本是 4.1+ 以上，需要对字符集进行初始化 */
			$charset=$conf['charset']?$conf['charset']:'utf8';
			if ($this->version > '4.1')
			{
				if ($charset != 'latin1')
				{
					mysql_query("SET character_set_connection=$charset, character_set_results=$charset, character_set_client=binary", $this->link);
				}
				if ($this->version > '5.0.1')
				{
					mysql_query("SET sql_mode=''", $this->link);
				}
			}
			/* 选择数据库 */
			if ($conf['db'])
			{
				if (mysql_select_db($conf['db'], $this->link) === false )
				{
					throw new Exception("Can't select MySQL database({$conf['db']})!");
					return false;
				}
				else
				{
					return true;
				}
			}
			else
			{
				return true;
			}
		}catch (Exception $e)
		{
			$this->debug($e);
		}

	}

	//sql常用操作
	/**
	 *
	 * @desc 插入数据
	 * @param String||Array $data
	 * @throws Exception
	 * @return Boolen
	 */
	public function insert(Array $data){
		if(is_array($data)){
			foreach($data as $key=>$value){
				$value=$this->check_value($value);
				$keys[]=$this->check_key($key);
				$values[]=$value;
			}
			$keys=implode(",",$keys);
			$values=implode(",",$values);
		}else{
			$array=explode("=",$data);
			$keys=$this->check_key($array[0]);
			$values=$this->check_value($array[1]);
		}
		try{
			if(empty($set)||empty($va)){
				throw new Exception("[ insert ] - error data");
			}else{
				$this->sql['action']="insert into {$this->tb} ({$keys}) values ({$values})";
				$this->execute();
				return $this->insertId();
			}
		}catch (Exception $e){
			$this->debug($e);
		}
	}

	/**
	 *
	 * @desc 删除数据
	 * @param String $where
	 * @return Boolen
	 */
	public function delete(){
		$this->sql['action']="delete from {$this->tb} ";
		if(!empty($this->conditions)){$this->buildCondition();}
		$this->execute();
		return $this->affectedRows();
	}

	/**
	 *
	 * @desc 更新数据
	 * @param String||Array $data
	 * @param String $where
	 * @return Boolen
	 */
	public function update($data){
		if(is_array($data)){
			foreach($data as $key=>$value){
				$value=$this->check_value($value);
				$key=$this->check_key($key);
				$sets.="{$key}={$value},";
			}
			$sets=substr($sets,0,-1);
		}else{
			$array=explode("=",$data);
			$value=$this->check_value($array[1]);
			$key=$this->check_key($array[0]);
			$sets="{$key}={$value},";

		}
		try{
			if(empty($sets)){
				throw new Exception("[ update ] - error data");
			}
			$this->sql['action']="update {$this->tb} set {$sets} ";
			if(!empty($this->conditions)){$this->buildCondition();}
			$this->execute();
			return $this->affectedRows();
		}catch (Exception $e){
			$this->debug($e);
		}
	}

	/**
	 *
	 * @desc 查询数据
	 * @param String $select
	 * @return $this
	 */
	public function select($select="*"){
		$this->sql['action']="select {$select} from {$this->tb}";
		if(!empty($this->conditions)){$this->buildCondition();}
		return $this;
	}

	//sql语句组件
	/**
	 *
	 * @desc 选择表
	 * @param String $tb
	 * @return $this
	 */
	public function tb($tb=''){
		$this->tb=self::pr($tb);
		return $this;
	}

	/**
	 *
	 * @desc where定位
	 * @param String $w
	 * @return $this
	 */
	public function buildCondition(){
		foreach($this->conditions as $condition){
			$where.="(";
			foreach($condition['condition'] as $c){
				$where.="{$c[0]} {$c[1]} {$c[2]}";
				if(!empty($c[3])){
					$where.=" {$c[3]}";
				}
			}
			$where.=")";
			if($condition['connect']){
				$where.=" {$condition['connect']} ";
			}
		}

		$this->sql['where']=" where {$where}";
		return $this;
	}

	/**
	 *
	 * @desc 添加条件组
	 * @param String $connect
	 * @return $this
	 */
	public function setConditionGround($connect=''){
		$this->conditions[$this->conditionGround]['connect']=$connect;
		$this->conditionGround++;
		return $this;
	}

	/**
	 *
	 * @desc 添加条件
	 * @param String $condition  //必须添加空格
	 * @param String $connect
	 * @example setCondition("id = 1",'and');
	 * @return $this
	 */
	public function setCondition($condition,$connect=''){
		$this->sql['conditions']=true;
		$condition=preg_replace('/(\w+)\s{0,}=\s{0,}(\w+)/', "\\1 = \\2", $condition);
		$array=explode(' ', $condition);
		$array[0]=$this->check_key($array[0]);
		$array[2]=$this->check_value($array[2]);
		$array[3]=$connect;
		if($array[1]=='like'||$array[1]=='='){
			$this->conditions[$this->conditionGround]['condition'][]=$array;
		}
		return $this;
	}

	/**
	 *
	 * @desc 排序
	 * @param String $order
	 * @param String $rank
	 * @return $this
	 */
	public function order($order,$rank="desc"){
		$this->sql['order']=" order by {$order} {$rank}";
		return $this;
	}

	/**
	 *
	 * @desc 数据输出量
	 * @param String $limit
	 * @return $this
	 */
	public function limit($limit){
		$this->sql['limit']=" limit {$limit}";
		return $this;
	}

	/**
	 *
	 * @desc 组建sql语句
	 * @return $this
	 */
	public function buildSql(){
		if($this->sql['conditions'])
		{
			$this->buildCondition();
		}
		$this->sql=$this->sql['action'].$this->sql['where'].$this->sql['order'].$this->sql['limit'];
		return $this;
	}

	//sql执行及结果
	/**
	 *
	 * @desc 执行sql
	 * @param String $sql
	 * @return Boolen
	 */
	public function execute(){
		if(is_array($this->sql)){
			$this->buildSql();
		}
		try{
			if(mysql_unbuffered_query($this->sql,$this->link)){
				return true;
			}else{
				throw new Exception($this->sql);
			};
		}catch(Exception $e){
			$this->debug($e);
		}
	}

	/**
	 * $desc 已过滤的安全sql语句
	 * @return $this
	 */
	private function safeQuery()
	{
		if(is_array($this->sql)){
			$this->buildSql();
		}
		try{
			if(@$result=mysql_query($this->sql,$this->link)){
				$this->result=$result;
				return $this;
			}else{
				throw new Exception($this->sql);
			}
		}catch(Exception $e){
			$this->debug($e);
		}
	}

	/**
	 *
	 * @desc 执行sql
	 * @param String $sql
	 * @return $this
	 */
	public function query($sql){
		$sql=trim($sql);
		try{
			$action = array_shift(explode(' ', $sql));
			if($action!='insert'&&$action!='delete'&&$action!='update'&&$action!='select')
			{
				throw new Exception("no allow action");
			}
		}catch (Exception $e)
		{
			exit('Message : '.$e->getMessage());
		}
		$str=$sql;
		$str=preg_replace("/#(\w+?)#/", $this->check_key("$1"), $str);
		$str=preg_replace("/:(.+?):/", $this->check_value("$1"), $str);
		$str=preg_replace("/\[(\w+?)\]/", $this->check_tb("$1"), $str);
		$this->sql=$str;
		$this->safeQuery();
		return $this;
	}

	/**
	 *
	 * @desc 获取一个记录
	 * @return String
	 */
	public function getOne(){
		$row=$this->getRow();
		if($row){
			return $row[0];
		}else{
			return null;
		}

	}

	/**
	 *
	 * @desc 获取一行记录
	 * @return Array
	 */
	public function getRow(){
		$row=mysql_fetch_array($this->result);
		if(!is_array($row)){
			return null;
		}else{
			return $row;
		}
	}

	/**
	 * @desc 获取一列记录
	 * @return Array
	 */
	public function getCol(){
		try{
			$arr=array();
			while($row=mysql_fetch_array($this->result,MYSQL_ASSOC)){
				array_push($arr,reset($row));
			}
			if(!is_array($arr)){
				throw new Exception("getCol error");
			}
			return $arr;
		}catch (Exception $e){
			exit('Message : '.$e->getMessage());
		}
	}

	/**
	 *
	 * @desc 获取所有记录
	 * @return Array
	 */
	public function getAll(){
		is_null($this->result)?$this->safeQuery():'';
		try{
			$arr=array();
			while($row=mysql_fetch_array($this->result,MYSQL_ASSOC)){
				array_push($arr,$row);
			}
			if(!is_array($arr)){
				throw new Exception("getAll error");
			}
			return $arr;
		}catch (Exception $e){
			exit('Message : '.$e->getMessage());
		}
	}

	/**
	 *
	 * @desc 统计数据
	 * @param String $select
	 * @return Int
	 */
	public function count($select="*"){
		try{
			$this->sql=preg_replace("/select\s[^^]+?\sfrom(.+)\slimit\s+?\d+\,\d+/","select count({$select}) from $1",$this->sql);
			$this->sql=preg_replace("/select\s[^^]+?\sfrom(.+)/","select count({$select}) from $1",$this->sql);

			//			echo $this->sql;
			$count=$this->query()->getOne();
			if(!is_numeric($count)){
				throw new Exception("count error");
			}
			return $count;
		}catch (Exception $e){
			exit('Message : '.$e->getMessage());
		}
	}

	/**
	 *
	 * @desc 获取最近一次sql插入的id
	 * @return Int
	 */
	public function insertId(){
		return mysql_insert_id();
	}

	/**
	 *
	 * @desc 获取最近一次sql影响行数
	 * @return Int
	 */
	public function affectedRows(){
		return mysql_affected_rows();
	}

	//数据库整体处理
	/**
	 *
	 * @desc 新建数据库
	 * @param String $database_name
	 * @return Boolen
	 */
	public function createDatabase($database) {
		try{
			if($database){
				$this->sql['action'] = "create database IF EXISTS `{$table}`";
			}else{
				throw new Exception('[ createDatabase ]:$database is empty');
			}
		}catch (Exception $e){
			$this->debug($e);
		}
		return $this->execute();
	}

	/**
	 *
	 * @desc 删除数据库
	 * @param String $database
	 */
	public function dorpDatabase($database){
		try{
			if($database){
				$this->sql['action']="dorp database if exists `{$database}`";
			}else{
				throw new Exception('[ dorpDatabase ]:$database is empty');
			}
		}catch (Exception $e){
			$this->debug($e);
		}
		return $this->execute();
	}

	/**
	 *
	 * @desc 新建表
	 * @param String $table
	 * @param Array $data
	 * @return Boolen
	 */
	public function createTable($table,$data){
		$this->sql['action']="CREATE TABLE `{$table}` (";
		foreach($data as $r){
			$this->sql['action'].="`{$r['field']}` {$r['type']}({$r['length']})";
			if($r['isNull']&&strtoupper($r['isNull'])!='NOT NULL'){
				$this->sql['action'].=" NOT NULL";
			}else{
				$this->sql['action'].=" NULL";
			}
			if($r['AUTO_INCREMENT']){
				$this->sql['action'].=" AUTO_INCREMENT";
			}
			if($r['DEFAULT']){
				$this->sql['action'].=" DEFAULT '{$r['DEFAULT']}'";
			}
			if($r['COMMENT']){
				$this->sql['action'].=" COMMENT '{$r['COMMENT']}'";
			}
			if($r['PRIMARY']){
				$keys="`{$r['field']}`,";
			}
			$this->sql['action'].=",";
		};
		$this->sql['action'].="PRIMARY KEY (".substr($keys, 0, -1).")";
		$this->sql['action'].=")";
		return $this->execute();
	}

	/**
	 *
	 * @desc 删除表
	 * @param String $table
	 */
	public function dorpTable($table){
		try{
			if($database){
				$this->sql['action']="dorp table if exists `{$table}`;";
			}else{
				throw new Exception('[ dorpDatabase ]:$database is empty');
			}
		}catch (Exception $e){
			$this->debug($e);
		}
		return $this->execute();
	}

	/**
	 *
	 * @desc 数据库列表
	 * @return Array
	 */
	public function showDatabases() {
		$this->sql['action']="show databases";
		return $this->query()->getAll();
	}

	/**
	 *
	 * @desc 指定数据库表列表
	 */
	public function showTables() {
		$this->sql['action']="show tables";
		return $this->query()->getAll();
	}

	/**
	 *
	 * @desc 获取服务器信息
	 * @param Int $num
	 * @return String
	 */
	public function mysqlServer($num = '') {
		switch ($num) {
			case 1 :
				return mysql_get_server_info(); //MySQL 服务器信息
				break;

			case 2 :
				return mysql_get_host_info(); //取得 MySQL 主机信息
				break;

			case 3 :
				return mysql_get_client_info(); //取得 MySQL 客户端信息
				break;

			case 4 :
				return mysql_get_proto_info(); //取得 MySQL 协议信息
				break;

			default :
				return mysql_get_client_info(); //默认取得mysql版本信息
		}
	}

	//事务处理
	/**
	 *
	 * @desc sql记录开始(先启用innodb引擎)
	 * @param String $tb
	 * @throws Exception
	 * @return NULL
	 */
	public function begin($tb=''){
		try{
			if(!empty($tb)){
				$status=$this->query("SHOW TABLE STATUS from  ".mysql::$config['db']."  where Name='".mysql::pr($tb)."'")->getRow();
				if($status['Engine']!='innodb'){
					throw new Exception("table Engine is not innodb");
				}
			}
			$this->query('BEGIN');
		}catch (Exception $e){
			$this->debug($e);
		}
	}

	/**
	 *
	 * @desc 执行记录的sql
	 * @return NULL
	 */
	public function commit(){
		$this->query('COMMIT');
	}

	/**
	 *
	 * @desc 回滚记录的sql
	 * @return NULL
	 */
	public function rollBack(){
		$this->query('ROLLBACK');
	}

	//优化项目
	/**
	 *
	 * @desc 释放资源
	 */
	public function free() {
		mysql_free_result($this->result);
	}

	/**
	 *
	 * @desc 变量安全
	 * @param str $value
	 */
	private function check_value($value){
		// 去除斜杠
		if (get_magic_quotes_gpc()){
			$value = stripslashes($value);
		}
		// 如果不是数字则加引号
		if (!is_numeric($value)){
			$value = "'" . mysql_real_escape_string($value) . "'";
		}
		return $value;
	}

	/**
	 *
	 * @desc key处理
	 * @param String $key
	 */
	private function check_key($key){
		return '`'.trim($key).'`';
	}

	private function check_tb($tb)
	{
		return self::$pr.$tb;
	}

	/**
	 *
	 * @desc 错误输出
	 * @param Object $e
	 */
	private function debug($e){
		if($this->debug){
			$mysql_errno=mysql_errno();
			$mysql_error=mysql_error();
			$html="<style>div{background:#999; margin-top:10px;}p{padding:5px; margin:0;}.p1{color:green;}.p2{color:#d93131;}td{color:#fff;}</style>";
			$html.="<div><p class='p1'>Message:".$e->getMessage()."</p>";
			$mysql_errno?$html.="<p class='p1'>SQL:".$mysql_errno."</p>":'';
			$mysql_error?$html.="<p class='p1'>Info:".$mysql_error."</p>":'';
			$html.="</div>";
			$html.="<div><table><tr><td class='p2'>line:".$e->getLine()."</td><td class='p2'>".$e->getFile()."</td><td></td></tr>";
			foreach($e->getTrace() as $r){
				$html.="<tr><td>line:{$r['line']}</td><td>{$r['file']}</td><td>[{$r['class']}]{$r['type']}{$r['public function']}</td></tr>";
			}
			$html.="</table></div>";
			exit($html);
		}else{
			exit();
		}
	}

	/**
	 *
	 * @desc 添加表前缀
	 * @param String $tb
	 */
	static public function pr($tb){
		return self::$pr.$tb;
	}

	/**
	 *
	 * @desc 关闭链接,释放资源
	 */
	public function __destruct(){
		!empty($this->result)?$this->free():'';
		$this->link?mysql_close($this->link):'';
	}

}

require ROOT.'data/config/mysql.config.php';
mysql::$config=$db_config;
//$db=new mysql();
?>